# 通过Python脚本，基于MySQL的DDL生成ODS层的建表语句
import re
import os

mysql_hive_type_mapping = {
    'varchar': 'string'
    , 'text': 'string'
    , 'double': 'double'
    , 'decimal': 'double'
}

update_column_map = {
    "base_acd_file": "sgfssj",
    "base_bd_drivinglicense": "gxsj",
    "base_bd_vehicle": "fzrq",
    "base_vio_force": "wfsj",
    "base_vio_surveil": "wfsj"
}

partition_column_map = {
    "base_acd_file": "sgbh",
    "base_bd_drivinglicense": "dabh",
    "base_bd_vehicle": "xh",
    "base_vio_force": "xh",
    "base_vio_surveil": "xh"
}

dwd_tables_set = {'base_acd_file', 'base_vio_force', 'base_vio_surveil'}
dim_tables_set = {'base_acd_filehuman', 'base_bd_drivinglicense', 'base_bd_vehicle'}


def convert_type_to_hive(type: str):
    return mysql_hive_type_mapping.get(type.split("(")[0], 'string')


for fileName in os.listdir('./mysql_ddl'):
    tableName = fileName.split(".")[0]
    with open(f'./mysql_ddl/{fileName}', mode='r', encoding='utf8') as f:
        sql = f.read()
        # print(sql)
        # 使用正则表达式提取表的列信息
        all_column_str = re.search("\((.*)\)", sql, re.S).group(1).strip()
        # 将每一列切分出来单独处理
        column_format = '{column_name} {column_type} comment {column_comment}'
        column_datax_format = '"name":"{column_name}","type":"{column_type}"'
        column_list = []
        column_name_list = []
        column_datax_list = []
        column_merge_list = []
        for column_str in all_column_str.split(","):
            # 从每一列中提取列名、类型、注释
            splits = column_str.strip().split(" ")
            column_name = splits[0]
            column_hive_type = convert_type_to_hive(splits[1])
            column_comment = splits[-1]
            column_list.append(
                column_format.format(column_name=column_name, column_type=column_hive_type,
                                     column_comment=column_comment))
            column_name_list.append(column_name.replace("`", '"'))
            column_merge_list.append(column_name)
            column_datax_list.append("{" + column_datax_format.format(column_name=column_name.replace("`", ""),
                                                                      column_type=column_hive_type) + "}")
        final_all_column_str = ",\n    ".join(column_list)
        ################ 生成ODS全量表建表语句 ################
        # 加载Hive的ODS建表语句模板
        ods_table_name_f = f"ods_{tableName}_d_f"
        with open('./template/ods_create_table_template.sql', mode='r', encoding='utf8') as t:
            final_sql = t.read().replace("${tableName}", ods_table_name_f).replace("${columns}", final_all_column_str)
            # 将最终的建表语句保存
            with open(f'../ddl/{ods_table_name_f}.sql', mode='w', encoding='utf8') as w:
                w.write(final_sql)
        ################ 生成ODS增量表建表语句 ################
        ods_table_name_i = f"ods_{tableName}_d_i"
        with open('./template/ods_create_incr_table_template.sql', mode='r', encoding='utf8') as t:
            final_sql = t.read().replace("${tableName}", ods_table_name_i).replace("${columns}", final_all_column_str)
            # 将最终的建表语句保存
            with open(f'../ddl/incr/{ods_table_name_i}.sql', mode='w', encoding='utf8') as w:
                w.write(final_sql)
        ################ 生成全量表DataX采集脚本 ################
        with open('./template/ods_datax_template.json', mode='r', encoding='utf8') as t:
            final_datax = t.read() \
                .replace("${mysql_table_name}", tableName) \
                .replace("${ods_table_name}", ods_table_name_f) \
                .replace("${colunm_names}", ",".join(column_name_list)) \
                .replace("${write_columns}", ",".join(column_datax_list))
            # 将最终的建表语句保存
            with open(f'../datax/{ods_table_name_f}.json', mode='w', encoding='utf8') as w:
                w.write(final_datax)
        # 需要做增量的表才生成增量采集脚本
        if tableName in update_column_map:
            ################ 生成增量表DataX采集脚本 ################
            with open('./template/ods_incr_datax_template.json', mode='r', encoding='utf8') as t:
                final_datax = t.read() \
                    .replace("${mysql_table_name}", tableName) \
                    .replace("${ods_table_name}", ods_table_name_i) \
                    .replace("${colunm_names}", ",".join(column_name_list)) \
                    .replace("${write_columns}", ",".join(column_datax_list)) \
                    .replace("${update_column}", update_column_map[tableName])
                # 将最终的建表语句保存
                with open(f'../datax/incr/{ods_table_name_i}.json', mode='w', encoding='utf8') as w:
                    w.write(final_datax)
            ################ 生成增量表及全量表合并SQL脚本 ################
            with open('./template/ods_merge_template.sql', mode='r', encoding='utf8') as t:
                final_datax = t.read() \
                    .replace("${fullTableName}", ods_table_name_f) \
                    .replace("${incrTableName}", ods_table_name_i) \
                    .replace("${partition}", partition_column_map[tableName]) \
                    .replace("${order}", update_column_map[tableName]) \
                    .replace("${columns}", ",".join(column_merge_list))
                # 将最终的建表语句保存
                with open(f'../merge/ods_{tableName}_merge.sql', mode='w', encoding='utf8') as w:
                    w.write(final_datax)
            ################ 生成增量表的启动Shell脚本 ################
            with open('./template/start_datax_incr_template.sh', mode='r', encoding='utf8') as t:
                final_shell = t.read().replace("${incrTableName}", ods_table_name_i)
                # 将最终的建表语句保存
                with open(f'../bin/datax/start_datax_{ods_table_name_i}.sh', mode='w', encoding='utf8', newline='\n') as w:
                    w.write(final_shell)
        ################ 生成DWD层事实表建表语句 ################
        dwd_table_name_f = f'dwd_{tableName}_msk_d_f'
        if tableName in dwd_tables_set:
            with open('./template/dwd_create_table_template.sql', mode='r', encoding='utf8') as t:
                final_sql = t.read().replace("${tableName}", dwd_table_name_f) \
                                    .replace("${columns}", final_all_column_str)
                # 将最终的建表语句保存
                with open(f'../../dwd/ddl/{dwd_table_name_f}.sql', mode='w', encoding='utf8') as w:
                    w.write(final_sql)
        ################ 生成DIM层维表建表语句 ################
        dim_table_name_f = f'dim_{tableName}_msk_d_f'
        if tableName in dim_tables_set:
            with open('./template/dim_create_table_template.sql', mode='r', encoding='utf8') as t:
                final_sql = t.read().replace("${tableName}", dim_table_name_f) \
                                    .replace("${columns}", final_all_column_str)
                # 将最终的建表语句保存
                with open(f'../../dim/ddl/{dim_table_name_f}.sql', mode='w', encoding='utf8') as w:
                    w.write(final_sql)